clear
capture log close
local path "D:\BJP\WageShocks\ReplicationFiles"
log using `path'\LogFiles\ACS_CleanUp.log, replace


*********************************************************************************
*************** Bring in 2005-2018 ACS Data into Stata **************************
*************** ACS individual data split over two files ************************
*********************************************************************************


**insheet using `path'\RawData\ACSFiles\Excel\ss05pus.csv
**keep pwgtp st puma powpuma migpuma migsp semp mig agep sex cow mar schl schg wagp wkhp wkw uwrk yoep indp naicsp occp rac1p racblk hisp racasn socp nativity eng
**rename uwrk wrk
**gen year=2005
**save `path'\RawData\ACSFiles\ACS_2005, replace
**clear

**forvalues k=6/8{
**insheet using `path'\RawData\ACSFiles\Excel\ss0`k'pusa.csv
**keep pwgtp st puma powpuma migpuma migsp semp mig agep sex cow mar schl schg wagp wkhp wkw uwrk yoep indp naicsp occp rac1p racblk hisp racasn socp nativity eng
**rename uwrk wrk
**save `path'\RawData\ACSFiles\temp\ACS_200`k'a, replace
**clear

**insheet using `path'\RawData\ACSFiles\Excel\ss0`k'pusb.csv
**keep pwgtp st puma powpuma migpuma migsp semp mig agep sex cow mar schl schg wagp wkhp wkw uwrk yoep indp naicsp occp rac1p racblk hisp racasn socp nativity eng
**rename uwrk wrk
**save `path'\RawData\ACSFiles\temp\ACS_200`k'b, replace
**append using `path'\RawData\ACSFiles\temp\ACS_200`k'a

**gen year=200`k'

**replace wkw=6 if wkw<=14 & year<2008
**replace wkw=5 if wkw>14 & wkw<=26 & year<2008
**replace wkw=4 if wkw>26 & wkw<=39 & year<2008
**replace wkw=3 if wkw>39 & wkw<=47 & year<2008
**replace wkw=2 if wkw>47 & wkw<=49 & year<2008
**replace wkw=1 if wkw>49 & year<2008

**save `path'\RawData\ACSFiles\ACS_200`k', replace
**clear
**}

**forvalues k=9/9{
**insheet using `path'\RawData\ACSFiles\Excel\ss0`k'pusa.csv
**keep pwgtp st puma powpuma migpuma migsp semp mig agep sex cow mar schl schg wagp wkhp wkw wrk yoep indp naicsp occp rac1p racblk hisp racasn socp nativity eng
**save `path'\RawData\ACSFiles\temp\ACS_200`k'a, replace
**clear

**insheet using `path'\RawData\ACSFiles\temp\ss0`k'pusb.csv
**keep pwgtp st puma powpuma migpuma migsp semp mig agep sex cow mar schl schg wagp wkhp wkw wrk yoep indp naicsp occp rac1p racblk hisp racasn socp nativity eng
**save `path'\RawData\ACSFiles\temp\ACS_200`k'b, replace
**append using `path'\RawData\ACSFiles\temp\ACS_200`k'a

**gen year=200`k'

**save `path'\RawData\ACSFiles\ACS_200`k', replace
**clear
**}

**forvalues k=10/19{
**insheet using `path'\RawData\ACSFiles\Excel\ss`k'pusa.csv
**keep pwgtp st puma powpuma migpuma migsp semp mig agep sex cow mar schl schg wagp wkhp wkw wrk yoep indp naicsp occp rac1p racblk hisp racasn socp nativity eng
**save `path'\RawData\ACSFiles\temp\ACS_20`k'a, replace
**clear

**insheet using `path'\RawData\ACSFiles\Excel\ss`k'pusb.csv
**keep pwgtp st puma powpuma migpuma migsp semp mig agep sex cow mar schl schg wagp wkhp wkw wrk yoep indp naicsp occp rac1p racblk hisp racasn socp nativity eng
**save `path'\RawData\ACSFiles\temp\ACS_20`k'b, replace
**append using `path'\RawData\ACSFiles\temp\ACS_20`k'a

**gen year=20`k'

**save `path'\RawData\ACSFiles\ACS_20`k', replace
**clear
**}

*******************************************
** Create Minimum Wage Variables for ACS **
*******************************************

use `path'\RawData\monthly_mw_data.dta
collapse (mean) mean_mw, by(statename year)
rename mean_mw mw
sort statename year

local lag_name "lmw l2mw l3mw l4mw"
forvalues i = 1/4{
    local name : word `i' of `lag_name'
	gen `name' = mw[_n -`i'] if state == state[_n - `i']
}

local lead_name "fmw f2mw f3mw"
forvalues i = 1/3{
    local name : word `i' of `lead_name'
	gen `name' = mw[_n +`i'] if state == state[_n + `i']
}

gen MW_Ch_Next_Yr=(ln(fmw)-ln(mw))
gen MW_Ch_This_Yr=(ln(mw)-ln(lmw))
gen MW_Ch_Last_Yr=(ln(lmw)-ln(l2mw))
gen MW_Ch_2Yrs_Ago=(ln(l2mw)-ln(l3mw))

keep if year>=2005 & year<=2018

merge m:1 statename using `path'\RawData\ACS_state_crosswalk
keep if _merge==3
gen inf_adj_state=(statename=="Arizona" | statename=="Colorado" | statename=="Florida" | statename=="Missouri" | statename=="Montana" | statename=="Ohio" | statename=="Oregon" | statename=="Vermont" | statename=="Washington" )
drop if inf_adj_state==1
drop inf_adj_state _merge
order statename year st
save `path'\FormattedData\ACS_mw_data, replace
clear

*************************************************************************
************** Grab Task Data from Formatted OES Files ******************
*************************************************************************
use `path'\FormattedData\OES_Analysis_Data
drop if year<2010
egen all_emp=sum(tot_emp), by(onetsoccode)
sort onetsoccode
keep share_r_cog share_r_man share_int share_nr_cog_a share_nr_man_ph share_r all_emp onetsoccode description
drop if onetsoccode==onetsoccode[_n-1]
save `path'\FormattedData\temp\TaskInfo_a, replace

** This creates all of these task values for more aggregated occupations included in the ACS **
rename onetsoccode soc_oes
merge m:1 soc_oes using `path'\RawData\OES_to_ACS_occ_xwalk
keep if _merge==3
drop _merge

egen tot_emp=sum(all_emp), by(soc_acs)
gen sh=all_emp/tot_emp

foreach var in "share_r_cog" "share_r_man" "share_int" "share_nr_cog_anal" "share_nr_man_phys" "share_r" {
gen sh_`var'=`var'*sh
}
collapse (sum) sh_share_r_cog sh_share_r_man sh_share_int sh_share_nr_cog_anal sh_share_nr_man_phys sh_share_r, by(soc_acs)

foreach var in "share_r_cog" "share_r_man" "share_int"  "share_nr_cog_anal" "share_nr_man_phys" "share_r" {
rename sh_`var' `var'
}

rename soc_acs onetsoccode
order onetsoccode share_nr_cog_anal share_r_cog share_r_man share_nr_man_phys share_r share_int
append using `path'\FormattedData\temp\TaskInfo_a
drop all_emp
sort onetsoccode
save `path'\FormattedData\TaskInfo_4ACS, replace
clear


*********************************************************************
********** Combine Raw ACS Data to Make Single Data File ************
*********************************************************************

** Bring in Data **
use `path'\RawData\ACSFiles\ACS_2005
forvalues k=2006/2018{
append using `path'\RawData\ACSFiles\ACS_`k'
}

** Limit to Currently Working Individuals or Individuals who Worked Over Previous Year **
keep if wrk==1 | wrk==2 & wagp~=. & wkhp~=. & wkw~=.

** Generate Imputed Wages
gen wks_wrk=51 if wkw==1
replace wks_wrk=49 if wkw==2
replace wks_wrk=44 if wkw==3
replace wks_wrk=34 if wkw==4
replace wks_wrk=21 if wkw==5
replace wks_wrk=8 if wkw==6

gen hourly_wage=wagp/(wkhp*wks_wrk)
replace hourly_wage=semp/(wkhp*wks_wrk) if cow==6 & hourly_wage==0
gen tot_hours=wkhp*wks_wrk

** Create Common Education Variable **
gen edcat=1 if schl<=8 & year<2008
replace edcat=2 if schl==9 & year<2008 
replace edcat=3 if schl>=10 & schl<=12 & year<2008 
replace edcat=4 if schl>=13 & schl~=. & year<2008

replace edcat=1 if schl<=15 & year>=2008
replace edcat=2 if schl>=16  & year>=2008 & schl<=17
replace edcat=3 if schl>=18 & schl<=20 & year>=2008
replace edcat=4 if schl>=21 & schl~=. & year>=2008

** Bring in state Info and MW Info **
merge m:1 st year using `path'\FormattedData\ACS_mw_data
keep if _merge==3
drop _merge

****************************
** Bring in Industry Info **
****************************

** First, account for a few industry coding changes between 2012 and 2013 survey **
replace indp=3095 if indp==3090
replace indp=3365 if indp==3360
replace indp=3875 if indp==3870
replace indp=3895 if indp==3890
replace indp=4195 if indp==4190
replace indp=4265 if indp==4260
replace indp=4795 if indp==4790
replace indp=5275 if indp==5270
replace indp=5295 if indp==5290

merge m:1 indp using `path'\RawData\acs_ind_descr
drop if _merge==2
drop _merge

replace hourly_wage=. if hourly_wage<0.25*mw
gen w2mw=hourly_wage/mw
gen w2mw2=w2mw
replace w2mw=. if year<2010

gen lw=(hourly_wage<(1.5*mw))
replace lw=. if year<2010
egen tot_lw_ind=sum(lw), by(indp)
replace indp=9999 if  tot_lw_ind<15000
replace ind_name_3d="zAll Other" if indp==9999

******************************
** Bring in Occupation Info **
******************************
rename socp acs_socp
merge m:1 acs_socp year using `path'\RawData\ACS_OverTime_occ_xwalk
keep if _merge==3
drop _merge

*****************************
*** Create Wage Intervals ***
*****************************

egen ave_w2mw_occ1=mean(w2mw), by(onetsoccode)
egen sd_w2mw_occ1=sd(w2mw), by(onetsoccode)
replace w2mw=. if w2mw>(ave_w2mw_occ1+(2*sd_w2mw_occ1))
replace w2mw=. if w2mw<(ave_w2mw_occ1-(2*sd_w2mw_occ1))

egen ave_w2mw_occ=mean(w2mw), by(onetsoccode statename)

** create occupation totals for different demographic groups **
gen uu=1
gen low_edu=(edcat<3)
gen young=(age<30)
gen na_poc= (rac1p>1 & rac1p~=6 & rac1p~=7 & rac1p~=. & racasn~=1)
gen female=(sex==2)
gen blk=(racblk==1)

egen tot_emp=sum(uu), by(statename ind_name_3d onetsoccode year)
egen tot_emp_w=sum(pwgtp), by(statename ind_name_3d onetsoccode year)

local groups "low_edu young na_poc female blk"
foreach y of local groups {
gen w_`y'=pwgtp*`y'
egen tot_emp_`y'=sum(`y'), by(statename ind_name_3d onetsoccode year)
egen tot_emp_w`y'=sum(w_`y'), by(statename ind_name_3d onetsoccode year)
}

gen tot_emp_he=tot_emp-tot_emp_low_edu
gen tot_emp_old=tot_emp-tot_emp_young
gen tot_emp_wa=tot_emp-tot_emp_na_poc
gen tot_emp_male=tot_emp-tot_emp_female
gen tot_emp_opoc=tot_emp_na_poc-tot_emp_blk

gen tot_emp_whe=tot_emp_w-tot_emp_wlow_edu
gen tot_emp_wold=tot_emp_w-tot_emp_wyoung
gen tot_emp_wwa=tot_emp_w-tot_emp_wna_poc
gen tot_emp_wmale=tot_emp_w-tot_emp_wf
gen tot_emp_wopoc=tot_emp_wna_poc-tot_emp_wblk

collapse (mean) tot_emp tot_emp_low_edu tot_emp_he tot_emp_young tot_emp_old tot_emp_na_poc tot_emp_wa tot_emp_female tot_emp_male tot_emp_opoc tot_emp_blk tot_emp_w tot_emp_wlow_edu tot_emp_whe tot_emp_wyoung tot_emp_wold tot_emp_wna_poc tot_emp_wwa tot_emp_wfemale tot_emp_wmale tot_emp_wopoc tot_emp_wblk ave_w2mw_occ, by(statename ind_name_3d onetsoccode year)

** Create Employment Changes overall and by demographic groups**
local groups "low_edu he na_poc wa young old female male blk opoc w wlow_edu whe wyoung wold wna_poc wwa wfemale wmale wblk wopoc"

forvalues j=2005/2015{
gen tte_`j'=tot_emp if year==`j'
egen te_`j'=mode(tte_`j'), by(ind_name_3d onetsoccode state)
drop tte_`j'

foreach y of local groups {
gen tte_`y'_`j'=tot_emp_`y' if year==`j'
egen te_`y'_`j'=mode(tte_`y'_`j'), by(ind_name_3d onetsoccode state)
drop tte_`y'_`j'
}
}

gen orig_emp4=.
gen orig_emp3=.
foreach y of local groups {
gen orig_emp4_`y'=.
gen orig_emp3_`y'=.
}

forvalues j=2005/2015{
replace orig_emp4=te_`j' if year==(`j'+4)
replace orig_emp3=te_`j' if year==(`j'+3)

foreach y of local groups {
replace orig_emp4_`y'=te_`y'_`j' if year==(`j'+4)
replace orig_emp3_`y'=te_`y'_`j' if year==(`j'+3)
}
}

gen lnch_occ_employed4=ln(tot_emp)-ln(orig_emp4) if year>=2009
gen lnch_occ_employed3=ln(tot_emp)-ln(orig_emp3) if year>=2008

foreach y of local groups {
gen lnch_occ_emp4_`y'=ln(tot_emp_`y')-ln(orig_emp4_`y') if year>=2009
gen lnch_occ_emp3_`y'=ln(tot_emp_`y')-ln(orig_emp3_`y') if year>=2008
drop te_`y'_20*
}


** Bring in Task Data, Create Wage Groupings, and Format Task Data**
destring onetsoccode, gen(soc2) force
rename onetsoccode onetsoccode2
rename soc2 onetsoccode
merge m:1 onetsoccode using `path'\FormattedData\TaskInfo_4ACS
gen oes_info=(_merge==3)
drop _merge

gen group=1 if ave_w2mw_occ<1.5
replace group=2 if ave_w2mw_occ>=1.5 & ave_w2mw_occ<2
replace group=3 if ave_w2mw_occ>=2 & ave_w2mw_occ<2.5
replace group=4 if ave_w2mw_occ>=2.5 & ave_w2mw_occ<6
drop if group==.

rename ave_w2mw_occ ave2_state_w2mw

foreach task_var in "share_r_cog" "share_r_man" "share_int" "share_r"{
gen mean_`task_var'=.
gen sd_`task_var'=.
forvalues k=1/4{
sum `task_var' if group==`k' & oes_info==1
gen m`k'_`task_var'=r(mean)
gen sd`k'_`task_var'=r(sd)
replace  mean_`task_var'=m`k'_`task_var' if group==`k' & oes_info==1
replace  sd_`task_var'=sd`k'_`task_var' if group==`k' & oes_info==1
drop m`k'_`task_var' sd`k'_`task_var'
}
}

gen RoutineSh=(share_r_cog-mean_share_r_cog)/sd_share_r_cog if oes_info==1
gen RoutineSh2=(share_r_man-mean_share_r_man)/sd_share_r_man  if oes_info==1
gen RoutineSh3=(share_r-mean_share_r)/sd_share_r  if oes_info==1
gen IntSh=(share_int-mean_share_int)/sd_share_int  if oes_info==1

drop mean_share_r* sd_share_r* mean_share_int sd_share_int

*** This creates wage-group specific variables for change in MW ****
merge m:1 st year using `path'\FormattedData\ACS_mw_data
keep if _merge==3
drop _merge

tab year, gen(year_dum)
tab state, gen(state_dum)
tab group, gen(group_dum)
tab ind_name_3d, gen(ind_dum)

forvalues g=1/4{
gen MW_Ch_Next_Yr_`g'=MW_Ch_Next_Yr*group_dum`g'
gen MW_Ch_This_Yr_`g'=MW_Ch_This_Yr*group_dum`g'
gen MW_Ch_Last_Yr_`g'=MW_Ch_Last_Yr*group_dum`g'
gen MW_Ch_2Yrs_Ago_`g'=MW_Ch_2Yrs_Ago*group_dum`g'

foreach task_var in "RoutineSh" "RoutineSh2" "RoutineSh3"  "IntSh" {
gen MW_Ch_Next_Yr_X_`task_var'_`g'=MW_Ch_Next_Yr*`task_var'*group_dum`g'
gen MW_Ch_This_Yr_X_`task_var'_`g'=MW_Ch_This_Yr*`task_var'*group_dum`g'
gen MW_Ch_Last_Yr_X_`task_var'_`g'=MW_Ch_Last_Yr*`task_var'*group_dum`g'
gen MW_Ch_2Yrs_Ago_X_`task_var'_`g'=MW_Ch_2Yrs_Ago*`task_var'*group_dum`g'
}
}

local groups2 "na_poc wa young old female male low_edu he blk opoc"
forvalues k=1/4{
forvalues j=5/14{
forvalues z=3/4{
gen lgr_oe`z'_`k'_`j'=group_dum`k'*ln(orig_emp`z')*year_dum`j'
foreach y of local groups2 {
gen lgr_oe`z'_`y'_`k'_`j'=group_dum`k'*ln(orig_emp`z'_`y')*year_dum`j'
}
}
foreach task_var in "RoutineSh" "RoutineSh2" "RoutineSh3"  "IntSh"  {
gen gr_`task_var'_`k'_`j'=group_dum`k'*`task_var'*year_dum`j' if oes_info==1
}
}
}

forvalues k=5/14{
forvalues j=1/42{
gen state_yr`k'_`j'=state_dum`j'*year_dum`k'
}
}

save `path'\FormattedData\Complete_ACS_Data, replace



log close



